﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DatabaseProvider
{
     public class MSSQL
    {
         private static SqlConnection _Conn;
         private static string _strConnection;
         /// <summary>
         /// Number of connection reached
         /// </summary>
         private static int numTries = 0;
         /// <summary>
         /// Set/ Get Maximum of retry connetion
         /// </summary>
         public static int MAX_TRIES = 3;//default retry 3 times

         /// <summary>
         /// Set/Get the connection string
         /// </summary>
         public static string ConnectionString {
             set
             {
                 _strConnection = value;
             }
             get
             {
                return _strConnection;
             }
         }
    
         /// <summary>
         /// Get/Set an sql connection 
         /// </summary>
         public static SqlConnection ConnnectionObj{
            set{_Conn=value;}
             get { return _Conn; }
         }

         /// <summary>
         /// Make new connection to db
         /// </summary>
         /// <returns>SqlConnection</returns>
         public static SqlConnection makeConnect()
         {
             SqlConnection con;
             try
             {
                  con = new SqlConnection(_strConnection);
             }
             catch (Exception e) {
                 throw e;
             }
             return con;
         }

         public static void openConnection(){
             try
             {
                 if (_Conn.State != System.Data.ConnectionState.Open)
                 {
                     _Conn.Open();
                 }
             }
             catch (Exception e) { 
             
             }
         }

         /// <summary>
         /// Execute command to get SqlDataReader
         /// </summary>
         /// <param name="_strCommand">SQL QUERY</param>
         /// <returns>SqlDataReader</returns>
         public static SqlDataReader ExecuteCommandGetDataReader(string _strCommand) {
             SqlDataReader drRerurn = null;

             if (_Conn == null) {
                 _Conn = makeConnect();
             }

             //Create command
             SqlCommand commandExecute = new SqlCommand(_strCommand, _Conn);
             try
             {

                 if (_Conn.State == System.Data.ConnectionState.Closed)
                 {
                     _Conn.Open();
                 }
                 drRerurn = commandExecute.ExecuteReader();
                 numTries = 0;
             }
             catch (Exception ex) {
                 throw ex;
             }
           return drRerurn;   
         }

         /// <summary>
         /// execute an query string without query: Insert, update,..
         /// </summary>
         /// <param name="_strCommand">SQL command</param>
         /// <returns>Number of row executed</returns>
         public static int ExecuteCommandNoneQuery(string _strCommand)
         {
             int _iRerurn = -1;

            // if (_Conn == null)
             //{
                 _Conn = makeConnect();
            // }
             //Create command
             SqlCommand commandExecute = new SqlCommand(_strCommand, _Conn);
             try
             {
                 // if(_Conn.State!= System.Data.ConnectionState.Open)
                 //{
                 _Conn.Open();
                 // }
                 _iRerurn = commandExecute.ExecuteNonQuery();
                 numTries = 0;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
             finally {
                 _Conn.Close();
             }
             return _iRerurn;
         }

         /// <summary>
         /// Close an SqlDataReader
         /// </summary>
         /// <param name="_reader">SqlDataReader need close</param>
         public static void CloseDatabaseObject(SqlDataReader _reader)
         {
             if (_reader != null)
             {
                 if (!_reader.IsClosed)
                 {
                     _reader.Close();
                 }
             }
             _reader = null;
         }
        
         /// <summary>
         /// Close an SqlConnection
         /// </summary>
         /// <param name="_sqlConn">SqlConnection need close</param>
         public static void CloseDatabaseObject(SqlConnection _sqlConn)
         {
             if (_sqlConn != null)
             {
                 if (_sqlConn.State == System.Data.ConnectionState.Open)
                 {
                     _sqlConn.Close();
                 }
             }
             _sqlConn = null;
         }
    
     }
}
